IF OBJECT_ID('dbo.GetInstitutions') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.GetInstitutions
    IF OBJECT_ID('dbo.GetInstitutions') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.GetInstitutions >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.GetInstitutions >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.GetInstitutions
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 5/24/2007
--
-- Description: Returns list of Principal Investigators from Insight
--
-- Parameters:  None
--              
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.GetInstitutions()
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 7/11/07     KC        Created
-- 8/15/07     KC        Added Security to limit results seen
-- 8/16/07     KC        Commented out Security for performance while testing
-- 8/17/07     KC        Security changed to Cached table
-- 9/13/07     KC        Changed to Info Table, and Org table 
-- 2/15/08     KC        Added Institutions that are linked at their own
--                       level to Agreements (like NWH)
-- 3/25/08     KC        Added option to Use Security
-- 3/26/08     KC        Do not pull back DFCI and DFPCC when ignoring Security
-- 12/17/08    KC        Security changed to Function
-- 01/05/08    KC        Security Function Perf Issues: use TempTable
-- 01/07/08    KC        Roll back temp table change.
--
-- =======================================================================
CREATE PROCEDURE dbo.GetInstitutions
     (@DomainUserId                INT,
     @IgnoreSecurity               CHAR(1)='N')
 
AS
BEGIN
     /*  Returned Fields for Dropdown
     --------------------------------
     InstitutionId            INT
     Institution              VARCHAR             
     InstitutionDescription   VARCHAR
     */

     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error            INT,
               @RowCount         INT

     -- Result Query  
     IF @IgnoreSecurity = 'Y'
          SELECT DISTINCT I.Id AS InstitutionId,
                    I.Name AS Institution,
                    I.Description AS Description
          FROM      dbo.AgrAgreement_Info AI INNER JOIN dbo.SvcEntity_FwkDomainOrganization_Map EDOM ON AI.SvcEntityId = EDOM.SvcEntityId 
                    INNER JOIN dbo.FwkDomainOrganization U ON U.Id = EDOM.FwkDomainOrganizationId
                    INNER JOIN dbo.FwkDomainOrganization D ON D.Id = U.DepartmentId 
                    INNER JOIN dbo.FwkDomainOrganization I ON I.Id = U.InstitutionId
          WHERE     I.FwkDomainOrganizationTypeId = 2 AND I.Id NOT IN (350,351) -- Ignore DFCI and DFPCC
          UNION
          SELECT    DISTINCT I.Id AS InstitutionId,
                    I.Name AS Institution,
                    I.Description AS Description
          FROM      dbo.AgrAgreement_Info AI INNER JOIN dbo.SvcEntity_FwkDomainOrganization_Map EDOM ON AI.SvcEntityId = EDOM.SvcEntityId 
                    INNER JOIN dbo.FwkDomainOrganization I ON I.Id  = EDOM.FwkDomainOrganizationId
          WHERE     I.FwkDomainOrganizationTypeId = 2 AND I.Id NOT IN (350,351) -- Ignore DFCI and DFPCC
          ORDER BY Institution   
     ELSE
          SELECT DISTINCT I.Id AS InstitutionId,
                    I.Name AS Institution,
                    I.Description AS Description
          FROM      dbo.AgrAgreement_Info AI INNER JOIN dbo.SvcEntity_FwkDomainOrganization_Map EDOM ON AI.SvcEntityId = EDOM.SvcEntityId 
                    INNER JOIN dbo.FwkDomainOrganization U ON U.Id = EDOM.FwkDomainOrganizationId
                    INNER JOIN dbo.FwkDomainOrganization D ON D.Id = U.DepartmentId 
                    INNER JOIN dbo.FwkDomainOrganization I ON I.Id = U.InstitutionId
                    -- Security Issues: Not all Users can see all Agreements and therefore all Departments
                    INNER JOIN dbo.fnGetSecurityAgreements(@DomainUserId) AUTH ON AI.AgreementId = AUTH.AgreementId
          WHERE     I.FwkDomainOrganizationTypeId = 2 
          UNION
          SELECT    DISTINCT I.Id AS InstitutionId,
                    I.Name AS Institution,
                    I.Description AS Description
          FROM      dbo.AgrAgreement_Info AI INNER JOIN dbo.SvcEntity_FwkDomainOrganization_Map EDOM ON AI.SvcEntityId = EDOM.SvcEntityId 
                    INNER JOIN dbo.FwkDomainOrganization I ON I.Id  = EDOM.FwkDomainOrganizationId
                    -- Security Issues: Not all Users can see all Agreements and therefore all Departments
                    INNER JOIN dbo.fnGetSecurityAgreements(@DomainUserId) AUTH ON AI.AgreementId = AUTH.AgreementId
          WHERE     I.FwkDomainOrganizationTypeId = 2 
          ORDER BY Institution   
     

     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
          RETURN -1

     -- Return Success
     RETURN 0
     
END
go

IF OBJECT_ID('dbo.GetInstitutions') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.GetInstitutions >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.GetInstitutions >>>'
go

GRANT EXECUTE ON dbo.GetInstitutions TO InsightReport,Insight
go

